﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    public partial class MainService : IEmployee
    {
        public DataSet Employee_GetForForm02(int _period, int _month, int _year, int _BranchID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_GetForForm02", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", _month);
            dbCmd.Parameters.AddWithValue("@year", _year);
            dbCmd.Parameters.AddWithValue("@period", _period);
            dbCmd.Parameters.AddWithValue("@BranchID", _BranchID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public DataSet Employee_GetAll()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public DataSet Employee_GetByDepartment(int DeptID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetByDepartment", dbConn);
            dbCmd.Parameters.AddWithValue("@DepartmentID", DeptID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public DataSet Employee_GetForReportByAge()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetForReportByAge", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_GetWithPageDepartment(out int ItemCount, int currentPage, int pageSize, int DepartmentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetWithPageDepartment", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_GetWithDepartment(int DepartmentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetWithDepartment", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public DataSet Employee_GetDangVien(int branchID, int DepartmentID, int _DangVien)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetDangVien", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);
            dbCmd.Parameters.AddWithValue("@DangVien", _DangVien);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public int Employee_CountTotalDateWork(DateTime _FromDate, DateTime _ToDate)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CountTotalDateWork", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@FromDate", _FromDate);
            dbCmd.Parameters.AddWithValue("@ToDate", _ToDate);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["Employee_TotalDateWork"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public bool Employee_CheckNewMember(int EmployeeID, DateTime _Date)
        {
            bool retVal = false;
            SqlConnection dbconn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CheckNewMember", dbconn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@Date", _Date);
            try
            {
                dbconn.Open();
                int Count = Convert.ToInt32(dbCmd.ExecuteScalar());
                retVal = Count > 0 ? true : false;
            }
            finally
            {
                dbconn.Close();
            }
            return retVal;
        }
        public bool Employee_UpdateSIBookNo(string _Old, string _New)
        {
            bool retVal = false;
            SqlConnection dbconn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_UpdateSIBookNo", dbconn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@SIBookNoOld", _Old);
            dbCmd.Parameters.AddWithValue("@SIBookNoNew", _New);
            try
            {
                dbconn.Open();
                int Count = Convert.ToInt32(dbCmd.ExecuteScalar());
                retVal = Count > 0 ? true : false;
            }
            finally
            {
                dbconn.Close();
            }
            return retVal;
        }
        public bool Employee_UpdateEmployeeCode(int EmployeeID, string _EmployeeCode)
        {
            bool retVal = false;
            SqlConnection dbconn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_UpdateEmployeeCode", dbconn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@EmployeeCode", _EmployeeCode);
            try
            {
                dbconn.Open();
                int Count = Convert.ToInt32(dbCmd.ExecuteScalar());
                retVal = Count > 0 ? true : false;
            }
            finally
            {
                dbconn.Close();
            }
            return retVal;
        }
        public int Employee_CountDateWorkNewMember(int EmployeeID, DateTime _ToDate)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CountDateWorkNewMember", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@ToDate", _ToDate);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["Employee_DateWork"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        public int Employee_Using(int EmployeeID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Employee_GetGirlWithDepartment(int DepartmentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetGirlWithDepartment", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Employee_GetListForReport(int Branch_ID, int Department_ID, string Employee_Sex, int Grade_ID, int From_Age, int To_Age, int _Month, int _Year, int _ToMonth, int _ToYear)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetListForReport", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_ID", Branch_ID);
            dbCmd.Parameters.AddWithValue("@Department_ID", Department_ID);
            if (Employee_Sex == "")
                dbCmd.Parameters.AddWithValue("@Employee_Sex", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_Sex", Convert.ToBoolean(Employee_Sex));
            dbCmd.Parameters.AddWithValue("@Grade_ID", Grade_ID);
            dbCmd.Parameters.AddWithValue("@From_Age", From_Age);
            dbCmd.Parameters.AddWithValue("@To_Age", To_Age);
            dbCmd.Parameters.AddWithValue("@Month", _Month);
            dbCmd.Parameters.AddWithValue("@Year", _Year);
            dbCmd.Parameters.AddWithValue("@ToMonth", _ToMonth);
            dbCmd.Parameters.AddWithValue("@ToYear", _ToYear);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Employee_GetAdvSearch(out int ItemCount, int _Branch_ID, string _No, string _CardNo, string _IDCardNo,
            string _FullName, DateTime _DOBFrom, DateTime _DOBTo, string _POB, string _Sex, int _Standard_ID, int _Position_ID, int _Grade_ID,
            int _ContractType_ID, string _Specialist, decimal _BasicSalaryFrom, decimal _BasicSalaryTo, string _LCNo, DateTime _LC_BeginDateFrom,
            DateTime _LC_BeginDateTo, DateTime _LC_FinishDateFrom, DateTime _LC_FinishDateTo, string _Address, string _Job, string _BookNo,
            string _MedicalNo, DateTime _StartDateFrom, DateTime _StartDateTo, string orderBy, string orderDirection, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetAdvSearch", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_ID", _Branch_ID);
            dbCmd.Parameters.AddWithValue("@Employee_Code", _No);
            dbCmd.Parameters.AddWithValue("@Employee_CardNo", _CardNo);
            dbCmd.Parameters.AddWithValue("@Employee_IDCardNo", _IDCardNo);
            dbCmd.Parameters.AddWithValue("@Employee_FullName", _FullName);
            dbCmd.Parameters.AddWithValue("@Standard_ID", _Standard_ID);
            dbCmd.Parameters.AddWithValue("@Position_ID", _Position_ID);
            dbCmd.Parameters.AddWithValue("@Grade_ID", _Grade_ID);
            dbCmd.Parameters.AddWithValue("@ContractType_ID", _ContractType_ID);
            dbCmd.Parameters.AddWithValue("@Employee_Specialist", _Specialist);
            dbCmd.Parameters.AddWithValue("@BasicSalaryFrom", _BasicSalaryFrom);
            dbCmd.Parameters.AddWithValue("@BasicSalaryTo", _BasicSalaryTo);
            dbCmd.Parameters.AddWithValue("@Employee_Address", _Address);
            dbCmd.Parameters.AddWithValue("@Employee_Job", _Job);
            dbCmd.Parameters.AddWithValue("@Employee_BookNo", _BookNo);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalNo", _MedicalNo);

            if (_DOBFrom == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_DateOfBirthFrom", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_DateOfBirthFrom", _DOBFrom);

            if (_DOBTo == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_DateOfBirthTo", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_DateOfBirthTo", _DOBTo);

            if (_StartDateTo == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_StartDateTo", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_StartDateTo", _StartDateTo);

            if (_StartDateFrom == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_StartDateFrom", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_StartDateFrom", _StartDateFrom);


            dbCmd.Parameters.AddWithValue("@Employee_PlaceOfBirth", _POB);
            dbCmd.Parameters.AddWithValue("@Employee_Sex", _Sex);
            dbCmd.Parameters.AddWithValue("@LCNo", _LCNo);

            if (_LC_FinishDateFrom == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_FinishDateFrom", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_FinishDateFrom", _LC_FinishDateFrom);

            if (_LC_FinishDateTo == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_FinishDateTo", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_FinishDateTo", _LC_FinishDateTo);

            if (_LC_BeginDateFrom == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_BeginDateFrom", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_BeginDateFrom", _LC_BeginDateFrom);

            if (_LC_BeginDateTo == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_BeginDateTo", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_BeginDateTo", _LC_BeginDateTo);
            dbCmd.Parameters.AddWithValue("@OrderBy", orderBy);
            dbCmd.Parameters.AddWithValue("@OrderDirection", orderDirection);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Employee_GetListNotActive(out int ItemCount, string keyWord, string orderBy, string orderDirection, int currentPage, int pageSize, int Branch_ID, int Department_ID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetListNotActive", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@KeyWord", keyWord);
            dbCmd.Parameters.AddWithValue("@OrderBy", orderBy);
            dbCmd.Parameters.AddWithValue("@OrderDirection", orderDirection);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@Branch_ID", Branch_ID);
            dbCmd.Parameters.AddWithValue("@Department_ID", Department_ID);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Employee_GetListTMP(out int ItemCount, string keyWord, string orderBy, string orderDirection, int currentPage, int pageSize, int Branch_ID, int Department_ID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetListTMP", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@KeyWord", keyWord);
            dbCmd.Parameters.AddWithValue("@OrderBy", orderBy);
            dbCmd.Parameters.AddWithValue("@OrderDirection", orderDirection);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@BranchID", Branch_ID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", Department_ID);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Employee_GetList(out int ItemCount, int branchID, int departmentID, string keyWord, string orderBy, string orderDirection, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@KeyWord", keyWord);
            dbCmd.Parameters.AddWithValue("@OrderBy", orderBy);
            dbCmd.Parameters.AddWithValue("@OrderDirection", orderDirection);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Employee_GetProbationList(out int ItemCount, int branchID, int departmentID, string keyWord, string orderBy, string orderDirection, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetProbationList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@KeyWord", keyWord);
            dbCmd.Parameters.AddWithValue("@OrderBy", orderBy);
            dbCmd.Parameters.AddWithValue("@OrderDirection", orderDirection);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_GetLabourContract(out int ItemCount, out int Total,int Day, DateTime Date, int Type, string orderBy, string orderDirection, int currentPage, int pageSize, int _BranchID, int _DepartmentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetLabourContract", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Date", Date);
            dbCmd.Parameters.AddWithValue("@Day", Day);
            dbCmd.Parameters.AddWithValue("@Type", Type);
            dbCmd.Parameters.AddWithValue("@OrderBy", orderBy);
            dbCmd.Parameters.AddWithValue("@OrderDirection", orderDirection);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@BranchID", _BranchID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", _DepartmentID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
                Total = Convert.ToInt32(retVal.Tables[2].Rows[0][0]);
                //SapHetHanHD = Convert.ToInt32(retVal.Tables[3].Rows[0][0]);
                //ChuaCoHD = Convert.ToInt32(retVal.Tables[4].Rows[0][0]);
                //SapHetHanTV = Convert.ToInt32(retVal.Tables[5].Rows[0][0]);
                //HetHanTV = Convert.ToInt32(retVal.Tables[6].Rows[0][0]);
                //SapHetHDDacBiet = Convert.ToInt32(retVal.Tables[7].Rows[0][0]);
               
                    
                  
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_GetList2(int branchID, int departmentID, int Type)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetList2", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@Type", Type);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        /// <summary>
        /// Cảnh báo sinh nhật trong tháng cho báo cáo
        /// </summary>
        /// <param name="departmentID"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public DataSet Employee_BirthDayForReport(int month, int branchID, int departmentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_BirthDayForReport", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        /// <summary>
        /// Cảnh báo sinh nhật trong tháng cho form
        /// </summary>
        /// <param name="ItemCount"></param>
        /// <param name="departmentID"></param>
        /// <param name="ToDate"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataSet Employee_BirthDay(out int ItemCount, int month, int branchID, int departmentID, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_BirthDay", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        /// <summary>
        /// Cảnh báo danh sách nghỉ thai sản để hiển thị trên form
        /// </summary>
        /// <param name="ItemCount"></param>
        /// <param name="departmentID"></param>
        /// <param name="ToDate"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataSet Employee_NghiThaiSanList(out int ItemCount,int branchID, int departmentID, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_NghiThaiSan", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_DanhSachVuotKhungList(out int ItemCount, int month, int year, int branchID, int departmentID, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_DanhSachVuotKhungList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@Year", year);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.SelectCommand.CommandTimeout = 10000;
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_DanhSachNgheList(out int ItemCount, int month, int year, int branchID, int departmentID, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_DanhSachNgheList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@Year", year);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);

                da.SelectCommand.CommandTimeout = 10000;

                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_FormEpChucDanh(string branchCode, int year)
        {
            return DataConnect.ExecuteDataset(Properties.Settings.Default.ConnectionString,"HRM_Employee_Mauepchucdanh",
                branchCode,
                year
            );
        }


        public DataSet Employee_Form02MucLuongDuyNhat(string branchCode, int year, int month, string typeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Form02_Mucluong", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchCode", branchCode);
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@Year", year);
            dbCmd.Parameters.AddWithValue("@TypeCode", typeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.SelectCommand.CommandTimeout = 10000;
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_Form02DieuChinhLuongTheoNhom(string branchCode, int year, int month, float amount)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Form02_Excel_TEST1", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchCode", branchCode);
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@Year", year);
            dbCmd.Parameters.AddWithValue("@Amount", amount);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.SelectCommand.CommandTimeout = 10000;
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        

        /// <summary>
        /// 
        /// <summary>
        /// 
        /// Cảnh báo DSCB sắp nâng lương (Hiển thị trên form)
        /// </summary>
        /// <param name="ItemCount">Số hàng</param>
        /// <param name="month">Số tháng cảnh báo trước</param>
        /// <param name="departmentID">Đơn vị trực thuộc</param>
        /// <param name="ToDate">Tính đến ngày</param>
        /// <param name="currentPage">Số trang hiện tại</param>
        /// <param name="pageSize">Số dòng trên trang</param>
        /// <returns>DataSet</returns>
        public DataSet Employee_IncreaseSalary(out int ItemCount, int _day, int branchID, int departmentID, DateTime ToDate, int currentPage, int pageSize, int _Type)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_IncreaseSalary", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Day", _day);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@ToDate", ToDate);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@Type", _Type);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        /// <summary>
        /// Cảnh báo DSCB sắp nghỉ hưu (Hiển thị trên form)
        /// </summary>
        /// <param name="ItemCount">Số hàng</param>
        /// <param name="month">Số tháng cảnh báo trước</param>
        /// <param name="departmentID">Đơn vị trực thuộc</param>
        /// <param name="ToDate">Tính đến ngày</param>
        /// <param name="MaleAge">Độ tuổi nghỉ hưu đối với Nam</param>
        /// <param name="FeMaleAge">Độ tuổi nghỉ hưu đối với Nữ</param>
        /// <param name="currentPage">Số trang hiện tại</param>
        /// <param name="pageSize">Số dòng trên trang</param>
        /// <returns>DataSet</returns>
        public DataSet Employee_Superannuation(out int ItemCount, int month, int branchID, int departmentID, DateTime ToDate, int MaleAge, int FeMaleAge, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Superannuation", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@ToDate", ToDate);
            dbCmd.Parameters.AddWithValue("@MaleAge", MaleAge);
            dbCmd.Parameters.AddWithValue("@FeMaleAge", FeMaleAge);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        /// <summary>
        /// Cảnh báo DSCB sắp nghỉ hưu (Hiển thị trên báo cáo)
        /// </summary>
        /// <param name="month"></param>
        /// <param name="departmentID"></param>
        /// <param name="ToDate"></param>
        /// <param name="MaleAge"></param>
        /// <param name="FeMaleAge"></param>
        /// <returns></returns>
        public DataSet Employee_SuperannuationForReport(int month, int departmentID, DateTime ToDate, int MaleAge, int FeMaleAge)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_SuperannuationForReport", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@ToDate", ToDate);
            dbCmd.Parameters.AddWithValue("@MaleAge", MaleAge);
            dbCmd.Parameters.AddWithValue("@FeMaleAge", FeMaleAge);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        /// <summary>
        /// Cảnh báo DSCB sắp nâng lương (Hiển thị trên báo cáo)
        /// </summary>
        /// <param name="month"></param>
        /// <param name="departmentID"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public DataSet Employee_IncreaseSalaryForReport(int month, int departmentID, DateTime ToDate)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_IncreaseSalaryForReport", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Month", month);
            dbCmd.Parameters.AddWithValue("@DepartmentID", departmentID);
            dbCmd.Parameters.AddWithValue("@ToDate", ToDate);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public EmployeeInfo Employee_GetMaleAndFemale(int BranchID)
        {
            EmployeeInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetMaleAndFemale", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", BranchID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new EmployeeInfo();
                    if (dr["Employee_Total"] != DBNull.Value)
                        retVal.EmployeeTotal = Convert.ToInt32(dr["Employee_Total"]);
                    if (dr["Employee_Male"] != DBNull.Value)
                        retVal.EmployeeMale = Convert.ToInt32(dr["Employee_Male"]);
                    if (dr["Employee_Female"] != DBNull.Value)
                        retVal.EmployeeFemale = Convert.ToInt32(dr["Employee_Female"]);

                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public EmployeeInfo Employee_GetInfo(int EmployeeID)
        {
            EmployeeInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", EmployeeID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new EmployeeInfo();
                    retVal.ID = Convert.ToInt32(dr["Employee_ID"]);
                    retVal.CardNo = dr["Employee_CardNo"] == DBNull.Value ? "" : dr["Employee_CardNo"].ToString();
                    retVal.Code = dr["Employee_Code"] == DBNull.Value ? "" : dr["Employee_Code"].ToString();
                    retVal.FirstName = Globals.DB_GetString(dr["Employee_FirstName"]);
                    retVal.LastName = Globals.DB_GetString(dr["Employee_LastName"]);
                    retVal.Alias = Globals.DB_GetString(dr["Employee_Alias"]);
                    retVal.Education = Globals.DB_GetValue<int>(dr["Employee_Education"],0);
                    retVal.DateOfBirth = dr["Employee_DateOfBirth"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_DateOfBirth"]);
                    retVal.PlaceOfBirth = Globals.DB_GetString(dr["Employee_PlaceOfBirth"]);
                    retVal.Address = Globals.DB_GetString(dr["Employee_Address"]);
                    retVal.NativeCountry = Globals.DB_GetString(dr["Employee_NativeCountry"]);
                    retVal.Phone = Globals.DB_GetString(dr["Employee_Phone"]);
                    retVal.Nationality = Globals.DB_GetValue<int>(dr["Employee_Nationality"],0);
                    retVal.PassportNo = dr["Employee_PassportNo"].ToString();
                    retVal.PassportIssueDate = dr["Employee_PassportIssueDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_PassportIssueDate"]);
                    retVal.PassportExpireDate = dr["Employee_PassportExpireDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_PassportExpireDate"]);
                    retVal.Sex = Convert.ToBoolean(dr["Employee_Sex"]);
                    retVal.MaterialStatusID = Globals.DB_GetValue<int>(dr["Employee_MaterialStatusID"],0);
                    retVal.ReligionID = Globals.DB_GetValue<int>(dr["Employee_ReligionID"],0);
                    retVal.EthlicID = Globals.DB_GetValue<int>(dr["Employee_EthlicID"], 0);
                    retVal.IDCardNo = Globals.DB_GetString(dr["Employee_IDCardNo"]);
                    retVal.IDCardIssueDate = dr["Employee_IDCardIssueDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_IDCardIssueDate"]);
                    retVal.IDCardIssueAuthority = dr["Employee_IDCardIssueAuthority"].ToString();
                    retVal.SIBookNo = Globals.DB_GetString(dr["Employee_SIBookNo"]);
                    retVal.SIBookIssueDate = dr["Employee_SIBookIssueDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_SIBookIssueDate"]);
                    retVal.SIBookIssueAuthority = Globals.DB_GetString(dr["Employee_SIBookIssueAuthority"]);
                    retVal.BankAccountNo = Globals.DB_GetString(dr["Employee_BankAccountNo"]);
                    retVal.BankName = Globals.DB_GetString(dr["Employee_BankName"]);
                    retVal.StartDate = dr["Employee_StartDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_StartDate"]);
                    retVal.EndDate = dr["Employee_EndDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_EndDate"]);
                    retVal.Description = Globals.DB_GetString(dr["Employee_Description"]);
                    retVal.UserName = Globals.DB_GetString(dr["Employee_UserName"]);
                    retVal.IsOfficer = Convert.ToBoolean(dr["Employee_Isofficer"]);
                    retVal.IsQuit = Convert.ToBoolean(dr["Employee_IsQuited"]);
                    retVal.MedicalNo = Globals.DB_GetString(dr["Employee_MedicalNo"]);
                    retVal.MedicalDate = dr["Employee_MedicalDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_MedicalDate"]);
                    retVal.MedicalPlace = Globals.DB_GetString(dr["Employee_MedicalPlace"]);
                    retVal.MedicalRegionCode = Globals.DB_GetString(dr["Employee_MedicalRegionCode"]);
                    retVal.Type = Globals.DB_GetValue<bool>(dr["Employee_Type"]);
                    retVal.LeaveWorkID = Globals.DB_GetValue<int>(dr["Employee_LeaveWorkID"],0);
                    retVal.UnemployIns = dr["Employee_UnemployIns"] != DBNull.Value ? Convert.ToBoolean(dr["Employee_UnemployIns"]) : false;
                    retVal.UnemployInsDate = dr["Employee_UnemployInsDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_UnemployInsDate"]);
                    retVal.Specialist = Globals.DB_GetString(dr["Employee_Specialist"]);
                    retVal.Culture = Globals.DB_GetString(dr["Employee_Culture"]);
                    retVal.PartyDate = dr["Employee_PartyDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_PartyDate"]);
                    retVal.ArmyFromDate = dr["Employee_ArmyFromDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_ArmyFromDate"]);
                    retVal.ArmyToDate = dr["Employee_ArmyToDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_ArmyToDate"]);
                    retVal.ArmyPosition = Globals.DB_GetString(dr["Employee_ArmyPosition"]);
                    //them tinh TNN, TNVK hay ko
                    retVal.UnJobFactor = dr["Employee_UnJobFactor"] != DBNull.Value ? Convert.ToBoolean(dr["Employee_UnJobFactor"]) : false;
                    retVal.UnOverFactor = dr["Employee_UnOverFactor"] != DBNull.Value ? Convert.ToBoolean(dr["Employee_UnOverFactor"]) : false;
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public EmployeeInfo Employee_GetInfoBySIBookNo(string _SIBookNo)
        {
            EmployeeInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetInfoBySIBookNo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_SIBookNo", _SIBookNo);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new EmployeeInfo();
                    retVal.ID = Convert.ToInt32(dr["Employee_ID"]);
                    retVal.CardNo = dr["Employee_CardNo"] == DBNull.Value ? "" : dr["Employee_CardNo"].ToString();
                    retVal.Code = dr["Employee_Code"] == DBNull.Value ? "" : dr["Employee_Code"].ToString();
                    retVal.FirstName = Globals.DB_GetString(dr["Employee_FirstName"]);
                    retVal.LastName = Globals.DB_GetString(dr["Employee_LastName"]);
                    retVal.Alias = Globals.DB_GetString(dr["Employee_Alias"]);
                    retVal.Education = Globals.DB_GetValue<int>(dr["Employee_Education"],0);
                    retVal.DateOfBirth = dr["Employee_DateOfBirth"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_DateOfBirth"]);
                    retVal.PlaceOfBirth = Globals.DB_GetString(dr["Employee_PlaceOfBirth"]);
                    retVal.Address = Globals.DB_GetString(dr["Employee_Address"]);
                    retVal.NativeCountry = Globals.DB_GetString(dr["Employee_NativeCountry"]);
                    retVal.Phone = Globals.DB_GetString(dr["Employee_Phone"]);
                    retVal.Nationality = Globals.DB_GetValue<int>(dr["Employee_Nationality"],0);
                    retVal.PassportNo = dr["Employee_PassportNo"].ToString();
                    retVal.PassportIssueDate = dr["Employee_PassportIssueDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_PassportIssueDate"]);
                    retVal.PassportExpireDate = dr["Employee_PassportExpireDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_PassportExpireDate"]);
                    retVal.Sex = Convert.ToBoolean(dr["Employee_Sex"]);
                    retVal.MaterialStatusID = Globals.DB_GetValue<int>(dr["Employee_MaterialStatusID"],0);
                    retVal.ReligionID = Globals.DB_GetValue<int>(dr["Employee_ReligionID"],0);
                    retVal.EthlicID = Globals.DB_GetValue<int>(dr["Employee_EthlicID"], 0);
                    retVal.IDCardNo = Globals.DB_GetString(dr["Employee_IDCardNo"]);
                    retVal.IDCardIssueDate = dr["Employee_IDCardIssueDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_IDCardIssueDate"]);
                    retVal.IDCardIssueAuthority = dr["Employee_IDCardIssueAuthority"].ToString();
                    retVal.SIBookNo = Globals.DB_GetString(dr["Employee_SIBookNo"]);
                    retVal.SIBookIssueDate = dr["Employee_SIBookIssueDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_SIBookIssueDate"]);
                    retVal.SIBookIssueAuthority = Globals.DB_GetString(dr["Employee_SIBookIssueAuthority"]);
                    retVal.BankAccountNo = Globals.DB_GetString(dr["Employee_BankAccountNo"]);
                    retVal.BankName = Globals.DB_GetString(dr["Employee_BankName"]);
                    retVal.StartDate = dr["Employee_StartDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_StartDate"]);
                    retVal.EndDate = dr["Employee_EndDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_EndDate"]);
                    retVal.Description = Globals.DB_GetString(dr["Employee_Description"]);
                    retVal.UserName = Globals.DB_GetString(dr["Employee_UserName"]);
                    retVal.IsOfficer = Convert.ToBoolean(dr["Employee_Isofficer"]);
                    retVal.IsQuit = Convert.ToBoolean(dr["Employee_IsQuited"]);
                    retVal.MedicalNo = Globals.DB_GetString(dr["Employee_MedicalNo"]);
                    retVal.MedicalDate = dr["Employee_MedicalDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_MedicalDate"]);
                    retVal.MedicalPlace = Globals.DB_GetString(dr["Employee_MedicalPlace"]);
                    retVal.MedicalRegionCode = Globals.DB_GetString(dr["Employee_MedicalRegionCode"]);
                    retVal.Type = Globals.DB_GetValue<bool>(dr["Employee_Type"]);
                    retVal.LeaveWorkID = Globals.DB_GetValue<int>(dr["Employee_LeaveWorkID"],0);
                    retVal.UnemployIns = dr["Employee_UnemployIns"] != DBNull.Value ? Convert.ToBoolean(dr["Employee_UnemployIns"]) : false;
                    retVal.UnemployInsDate = dr["Employee_UnemployInsDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_UnemployInsDate"]);
                    retVal.Specialist = Globals.DB_GetString(dr["Employee_Specialist"]);
                    retVal.Culture = Globals.DB_GetString(dr["Employee_Culture"]);
                    retVal.PartyDate = dr["Employee_PartyDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_PartyDate"]);
                    retVal.ArmyFromDate = dr["Employee_ArmyFromDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_ArmyFromDate"]);
                    retVal.ArmyToDate = dr["Employee_ArmyToDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Employee_ArmyToDate"]);
                    retVal.ArmyPosition = Globals.DB_GetString(dr["Employee_ArmyPosition"]);
                    //them tinh TNN, TNVK hay ko
                    retVal.UnJobFactor = dr["Employee_UnJobFactor"] != DBNull.Value ? Convert.ToBoolean(dr["Employee_UnJobFactor"]) : false;
                    retVal.UnOverFactor = dr["Employee_UnOverFactor"] != DBNull.Value ? Convert.ToBoolean(dr["Employee_UnOverFactor"]) : false;
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int Employee_CheckExist(int EmployeeID, string EmployeeCode, int _BranchID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CheckExist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@Employee_Code", EmployeeCode);
            dbCmd.Parameters.AddWithValue("@BranchID", _BranchID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["Employee_ID"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int Employee_CheckExist2(int _BranchID, DateTime _DateOfBirth, string _EmployeeCardNo, string _EmployeeSiBookNo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CheckExist2", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _BranchID);
            dbCmd.Parameters.AddWithValue("@DateOfBirth", _DateOfBirth);
            dbCmd.Parameters.AddWithValue("@EmployeeCardNo", _EmployeeCardNo);
            dbCmd.Parameters.AddWithValue("@EmployeeSiBookNo", _EmployeeSiBookNo);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["Employee_ID"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int Employee_CheckIDCard(int EmployeeID, string Employee_IDCardNo, int _BranchID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CheckIDCard", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@Employee_IDCardNo", Employee_IDCardNo);
            dbCmd.Parameters.AddWithValue("@Branch_ID", _BranchID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["Employee_ID"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        
        public int Employee_CheckExistSIBookNo(int EmployeeID, string SIBookNo, int _BranchID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CheckExistSIBookNo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@Employee_SIBookNo", SIBookNo);
            dbCmd.Parameters.AddWithValue("@BranchID", _BranchID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["Employee_ID"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        
        public int Employee_CodeExist(int EmployeeID, string Code)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_CodeExist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@Employee_Code", Code);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["Employee_ID"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int Employee_Insert(EmployeeInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            if (eInfo.CardNo.Trim().Length == 0)
                dbCmd.Parameters.AddWithValue("@Employee_CardNo", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_CardNo", eInfo.CardNo);
            if (eInfo.Code.Trim().Length == 0)
                dbCmd.Parameters.AddWithValue("@Employee_Code", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_Code", eInfo.Code);
            dbCmd.Parameters.AddWithValue("@Employee_Alias", eInfo.Alias);
            dbCmd.Parameters.AddWithValue("@Employee_FirstName", eInfo.FirstName);
            dbCmd.Parameters.AddWithValue("@Employee_LastName", eInfo.LastName);
            dbCmd.Parameters.AddWithValue("@Employee_Education", eInfo.Education);
            dbCmd.Parameters.AddWithValue("@Employee_DateOfBirth", eInfo.DateOfBirth);
            dbCmd.Parameters.AddWithValue("@Employee_EthlicID", eInfo.EthlicID);
            dbCmd.Parameters.AddWithValue("@Employee_MaterialStatusID", eInfo.MaterialStatusID); ;
            dbCmd.Parameters.AddWithValue("@Employee_ReligionID", eInfo.ReligionID); ;
            dbCmd.Parameters.AddWithValue("@Employee_Sex", eInfo.Sex);
            dbCmd.Parameters.AddWithValue("@Employee_PlaceOfBirth", eInfo.PlaceOfBirth);
            dbCmd.Parameters.AddWithValue("@Employee_Address", eInfo.Address);
            dbCmd.Parameters.AddWithValue("@Employee_NativeCountry", eInfo.NativeCountry);
            dbCmd.Parameters.AddWithValue("@Employee_Phone", eInfo.Phone);
            dbCmd.Parameters.AddWithValue("@Employee_IDCardNo", eInfo.IDCardNo);
            dbCmd.Parameters.AddWithValue("@Employee_Nationality", eInfo.Nationality);
            dbCmd.Parameters.AddWithValue("@Employee_PassportNo", eInfo.PassportNo);
            if (eInfo.PassportIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PassportIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PassportIssueDate", eInfo.PassportIssueDate);

            if (eInfo.PassportExpireDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PassportExpireDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PassportExpireDate", eInfo.PassportExpireDate);

            if (eInfo.IDCardIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueDate", eInfo.IDCardIssueDate);

            dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueAuthority", eInfo.IDCardIssueAuthority);
            dbCmd.Parameters.AddWithValue("@Employee_SIBookNo", eInfo.SIBookNo);
            if (eInfo.SIBookIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_SIBookIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_SIBookIssueDate", eInfo.SIBookIssueDate);
            dbCmd.Parameters.AddWithValue("@EMPLOYEE_SIBOOKISSUEAUTHORITY", eInfo.SIBookIssueAuthority);
            dbCmd.Parameters.AddWithValue("@Employee_BankAccountNo", eInfo.BankAccountNo);
            dbCmd.Parameters.AddWithValue("@Employee_BankName", eInfo.BankName);

            if (eInfo.StartDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_StartDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_StartDate", eInfo.StartDate);

            if (eInfo.EndDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_EndDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_EndDate", eInfo.EndDate);

            dbCmd.Parameters.AddWithValue("@Employee_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@Employee_IsOfficer", eInfo.IsOfficer);
            dbCmd.Parameters.AddWithValue("@Employee_IsQuited", eInfo.IsQuit);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalNo", eInfo.MedicalNo);
            if (eInfo.MedicalDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_MedicalDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_MedicalDate", eInfo.MedicalDate);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalPlace", eInfo.MedicalPlace);
            dbCmd.Parameters.AddWithValue("@Employee_Type", eInfo.Type);
            dbCmd.Parameters.AddWithValue("@Employee_LeaveWorkID", eInfo.LeaveWorkID);
            dbCmd.Parameters.AddWithValue("@Employee_UnemployIns", eInfo.UnemployIns);
            if (eInfo.UnemployInsDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_UnemployInsDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_UnemployInsDate", eInfo.UnemployInsDate);
            dbCmd.Parameters.AddWithValue("@Employee_Specialist", eInfo.Specialist);
            dbCmd.Parameters.AddWithValue("@Employee_Culture", eInfo.Culture);
            dbCmd.Parameters.AddWithValue("@Employee_IsInitial", eInfo.IsInitial);

            if (eInfo.PartyDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PartyDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PartyDate", eInfo.PartyDate);
            if (eInfo.ArmyFromDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_ArmyFromDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_ArmyFromDate", eInfo.ArmyFromDate);
            if (eInfo.ArmyToDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_ArmyToDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_ArmyToDate", eInfo.ArmyToDate);
            dbCmd.Parameters.AddWithValue("@Employee_ArmyPosition", eInfo.ArmyPosition);
            dbCmd.Parameters.AddWithValue("@Employee_BranchID", eInfo.BranchID);
            
            //hiepdh: them cos tinh TNN, TNVK hay ko
            dbCmd.Parameters.AddWithValue("@Employee_UnJobFactor", eInfo.UnJobFactor);
            dbCmd.Parameters.AddWithValue("@Employee_UnOverFactor", eInfo.UnOverFactor);

            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
    
        //y hệt như insert, chỉ khác là import thì ko sinh số sổ tạm
        public int Employee_Import(EmployeeInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Import", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            if (eInfo.CardNo.Trim().Length == 0)
                dbCmd.Parameters.AddWithValue("@Employee_CardNo", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_CardNo", eInfo.CardNo);
            if (eInfo.Code.Trim().Length == 0)
                dbCmd.Parameters.AddWithValue("@Employee_Code", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_Code", eInfo.Code);
            dbCmd.Parameters.AddWithValue("@Employee_Alias", eInfo.Alias);
            dbCmd.Parameters.AddWithValue("@Employee_FirstName", eInfo.FirstName);
            dbCmd.Parameters.AddWithValue("@Employee_LastName", eInfo.LastName);
            dbCmd.Parameters.AddWithValue("@Employee_Education", eInfo.Education);
            dbCmd.Parameters.AddWithValue("@Employee_DateOfBirth", eInfo.DateOfBirth);
            dbCmd.Parameters.AddWithValue("@Employee_EthlicID", eInfo.EthlicID);
            dbCmd.Parameters.AddWithValue("@Employee_MaterialStatusID", eInfo.MaterialStatusID); ;
            dbCmd.Parameters.AddWithValue("@Employee_ReligionID", eInfo.ReligionID); ;
            dbCmd.Parameters.AddWithValue("@Employee_Sex", eInfo.Sex);
            dbCmd.Parameters.AddWithValue("@Employee_PlaceOfBirth", eInfo.PlaceOfBirth);
            dbCmd.Parameters.AddWithValue("@Employee_Address", eInfo.Address);
            dbCmd.Parameters.AddWithValue("@Employee_NativeCountry", eInfo.NativeCountry);
            dbCmd.Parameters.AddWithValue("@Employee_Phone", eInfo.Phone);
            dbCmd.Parameters.AddWithValue("@Employee_IDCardNo", eInfo.IDCardNo);
            dbCmd.Parameters.AddWithValue("@Employee_Nationality", eInfo.Nationality);
            dbCmd.Parameters.AddWithValue("@Employee_PassportNo", eInfo.PassportNo);
            if (eInfo.PassportIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PassportIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PassportIssueDate", eInfo.PassportIssueDate);

            if (eInfo.PassportExpireDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PassportExpireDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PassportExpireDate", eInfo.PassportExpireDate);

            if (eInfo.IDCardIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueDate", eInfo.IDCardIssueDate);

            dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueAuthority", eInfo.IDCardIssueAuthority);
            dbCmd.Parameters.AddWithValue("@Employee_SIBookNo", eInfo.SIBookNo);
            if (eInfo.SIBookIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_SIBookIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_SIBookIssueDate", eInfo.SIBookIssueDate);
            dbCmd.Parameters.AddWithValue("@EMPLOYEE_SIBOOKISSUEAUTHORITY", eInfo.SIBookIssueAuthority);
            dbCmd.Parameters.AddWithValue("@Employee_BankAccountNo", eInfo.BankAccountNo);
            dbCmd.Parameters.AddWithValue("@Employee_BankName", eInfo.BankName);

            if (eInfo.StartDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_StartDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_StartDate", eInfo.StartDate);

            if (eInfo.EndDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_EndDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_EndDate", eInfo.EndDate);

            dbCmd.Parameters.AddWithValue("@Employee_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@Employee_IsOfficer", eInfo.IsOfficer);
            dbCmd.Parameters.AddWithValue("@Employee_IsQuited", eInfo.IsQuit);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalNo", eInfo.MedicalNo);
            if (eInfo.MedicalDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_MedicalDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_MedicalDate", eInfo.MedicalDate);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalPlace", eInfo.MedicalPlace);
            dbCmd.Parameters.AddWithValue("@Employee_Type", eInfo.Type);
            dbCmd.Parameters.AddWithValue("@Employee_LeaveWorkID", eInfo.LeaveWorkID);
            dbCmd.Parameters.AddWithValue("@Employee_UnemployIns", eInfo.UnemployIns);
            if (eInfo.UnemployInsDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_UnemployInsDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_UnemployInsDate", eInfo.UnemployInsDate);
            dbCmd.Parameters.AddWithValue("@Employee_Specialist", eInfo.Specialist);
            dbCmd.Parameters.AddWithValue("@Employee_Culture", eInfo.Culture);
            dbCmd.Parameters.AddWithValue("@Employee_IsInitial", eInfo.IsInitial);

            if (eInfo.PartyDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PartyDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PartyDate", eInfo.PartyDate);
            if (eInfo.ArmyFromDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_ArmyFromDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_ArmyFromDate", eInfo.ArmyFromDate);
            if (eInfo.ArmyToDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_ArmyToDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_ArmyToDate", eInfo.ArmyToDate);
            dbCmd.Parameters.AddWithValue("@Employee_ArmyPosition", eInfo.ArmyPosition);
            dbCmd.Parameters.AddWithValue("@Employee_BranchID", eInfo.BranchID);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Employee_Update(EmployeeInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", eInfo.ID);
            if (eInfo.CardNo.Trim().Length == 0)
                dbCmd.Parameters.AddWithValue("@Employee_CardNo", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_CardNo", eInfo.CardNo);

            if (eInfo.Code.Trim().Length == 0)
                dbCmd.Parameters.AddWithValue("@Employee_Code", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_Code", eInfo.Code);
            dbCmd.Parameters.AddWithValue("@Employee_Alias", eInfo.Alias);
            dbCmd.Parameters.AddWithValue("@Employee_FirstName", eInfo.FirstName);
            dbCmd.Parameters.AddWithValue("@Employee_LastName", eInfo.LastName);
            dbCmd.Parameters.AddWithValue("@Employee_Education", eInfo.Education);
            dbCmd.Parameters.AddWithValue("@Employee_DateOfBirth", eInfo.DateOfBirth);
            dbCmd.Parameters.AddWithValue("@Employee_EthlicID", eInfo.EthlicID);
            dbCmd.Parameters.AddWithValue("@Employee_MaterialStatusID", eInfo.MaterialStatusID); ;
            dbCmd.Parameters.AddWithValue("@Employee_ReligionID", eInfo.ReligionID); ;
            dbCmd.Parameters.AddWithValue("@Employee_Sex", eInfo.Sex);
            dbCmd.Parameters.AddWithValue("@Employee_PlaceOfBirth", eInfo.PlaceOfBirth);
            dbCmd.Parameters.AddWithValue("@Employee_Address", eInfo.Address);
            dbCmd.Parameters.AddWithValue("@Employee_NativeCountry", eInfo.NativeCountry);
            dbCmd.Parameters.AddWithValue("@Employee_Phone", eInfo.Phone);
            dbCmd.Parameters.AddWithValue("@Employee_IDCardNo", eInfo.IDCardNo);
            dbCmd.Parameters.AddWithValue("@Employee_Nationality", eInfo.Nationality);
            dbCmd.Parameters.AddWithValue("@Employee_PassportNo", eInfo.PassportNo);
            if (eInfo.PassportIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PassportIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PassportIssueDate", eInfo.PassportIssueDate);

            if (eInfo.PassportExpireDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PassportExpireDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PassportExpireDate", eInfo.PassportExpireDate);

            if (eInfo.IDCardIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueDate", eInfo.IDCardIssueDate);

            dbCmd.Parameters.AddWithValue("@Employee_IDCardIssueAuthority", eInfo.IDCardIssueAuthority);
            dbCmd.Parameters.AddWithValue("@Employee_SIBookNo", eInfo.SIBookNo);
            if (eInfo.SIBookIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_SIBookIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_SIBookIssueDate", eInfo.SIBookIssueDate);

            dbCmd.Parameters.AddWithValue("@Employee_SIBookIssueAuthority", eInfo.SIBookIssueAuthority);
            dbCmd.Parameters.AddWithValue("@Employee_BankAccountNo", eInfo.BankAccountNo);
            dbCmd.Parameters.AddWithValue("@Employee_BankName", eInfo.BankName);

            if (eInfo.StartDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_StartDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_StartDate", eInfo.StartDate);

            if (eInfo.EndDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_EndDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_EndDate", eInfo.EndDate);

            dbCmd.Parameters.AddWithValue("@Employee_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@Employee_IsOfficer", eInfo.IsOfficer);
            dbCmd.Parameters.AddWithValue("@Employee_IsQuited", eInfo.IsQuit);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalNo", eInfo.MedicalNo);
            if (eInfo.MedicalDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_MedicalDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_MedicalDate", eInfo.MedicalDate);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalPlace", eInfo.MedicalPlace);
            dbCmd.Parameters.AddWithValue("@Employee_MedicalRegionCode", eInfo.MedicalRegionCode);
            dbCmd.Parameters.AddWithValue("@Employee_Type", eInfo.Type);
            dbCmd.Parameters.AddWithValue("@Employee_LeaveWorkID", eInfo.LeaveWorkID);
            dbCmd.Parameters.AddWithValue("@Employee_UnemployIns", eInfo.UnemployIns);
            if (eInfo.UnemployInsDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_UnemployInsDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_UnemployInsDate", eInfo.UnemployInsDate);
            dbCmd.Parameters.AddWithValue("@Employee_Specialist", eInfo.Specialist);
            dbCmd.Parameters.AddWithValue("@Employee_Culture", eInfo.Culture);
            if (eInfo.PartyDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_PartyDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_PartyDate", eInfo.PartyDate);
            if (eInfo.ArmyFromDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_ArmyFromDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_ArmyFromDate", eInfo.ArmyFromDate);
            if (eInfo.ArmyToDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Employee_ArmyToDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Employee_ArmyToDate", eInfo.ArmyToDate);
            dbCmd.Parameters.AddWithValue("@Employee_ArmyPosition", eInfo.ArmyPosition);
            dbCmd.Parameters.AddWithValue("@Employee_BranchID", eInfo.BranchID);

            //hiepdh: them cos tinh TNN, TNVK hay ko
            dbCmd.Parameters.AddWithValue("@Employee_UnJobFactor", eInfo.UnJobFactor);
            dbCmd.Parameters.AddWithValue("@Employee_UnOverFactor", eInfo.UnOverFactor);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Employee_UpdateUserName(int EmployeeID, string _UserName)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_UpdateUserName", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@Employee_UserName", _UserName);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Employee_UpdateImage(int EmployeeID, byte[] _Image)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_UpdateImage", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            dbCmd.Parameters.AddWithValue("@Image", _Image);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public bool Employee_UpdateImageNull(int EmployeeID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_UpdateImageNull", dbConn);
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            dbCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public byte[] Employee_GetImage(int EmployeeID)
        {
            byte[] retVal = null;
            SqlConnection dbconn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbcmd = new SqlCommand("HRM_Employee_GetImage", dbconn);
            dbcmd.CommandType = CommandType.StoredProcedure;
            dbcmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            SqlDataReader dr = null;
            try
            {
                dbconn.Open();
                dr = dbcmd.ExecuteReader();
                if (dr.Read())
                {
                    //Get image data.
                    if (dr["Employee_Image"].ToString() == "")
                        retVal = null;
                    else
                        retVal = (byte[])dr["Employee_Image"];
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbconn.Close();
            }
            return retVal;
        }

        public bool Employee_Delete(int EmployeeID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Employee_ID", EmployeeID);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        //Báo cáo:

        public DataSet Employee_GetAllWithDepartment(int DepartmentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetAllWithDepartment", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_GetAllWithBranch(int BranchID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetAllWithBranch", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", BranchID);

            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public EmployeeInfo Employee_GetMaxEmployeeCode(int Branch_ID)
        {
            EmployeeInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_GetMaxEmployeeCode", dbConn);
            dbCmd.Parameters.AddWithValue("@BranchID", Branch_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new EmployeeInfo();
                    retVal.ID = Convert.ToInt32(dr["Employee_ID"]);
                    retVal.CardNo = dr["Employee_CardNo"].ToString();
                    retVal.Code = dr["Employee_Code"].ToString();
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_TotalYears(int _branch, int _year)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_TotalYears", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _branch);
            dbCmd.Parameters.AddWithValue("@Year", _year);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_Total(int _branch, int _year)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Total", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _branch);
            dbCmd.Parameters.AddWithValue("@Year", _year);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_Enumerate(int _branch)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Enumerate", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _branch);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_Descrease(int _branch, int _month, int _year, int _tomonth, int _toyear)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_EmployeeDescrease", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _branch);
            dbCmd.Parameters.AddWithValue("@Month", _month);
            dbCmd.Parameters.AddWithValue("@Year", _year);
            dbCmd.Parameters.AddWithValue("@ToMonth", _tomonth);
            dbCmd.Parameters.AddWithValue("@ToYear", _toyear);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public DataSet Employee_Declaration(int _branch, int _month, int _year, int _tomonth, int _toyear)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Declaration", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _branch);
            dbCmd.Parameters.AddWithValue("@Month", _month);
            dbCmd.Parameters.AddWithValue("@Year", _year);
            dbCmd.Parameters.AddWithValue("@ToMonth", _tomonth);
            dbCmd.Parameters.AddWithValue("@ToYear", _toyear);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Employee_Standard(int _branch, int _year)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Employee_Standard", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _branch);
            dbCmd.Parameters.AddWithValue("@Year", _year);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public DataSet EmplooyeeNotLabourContractList_Report(int _branch)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_EmplooyeeNotLabourContractList_Report", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", _branch);          
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public string Employee_SinhSoSoTam()
        {
            string retVal = "";
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_TaoSoSoTam", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToString(dr["SoSoTam"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
    }
}
